﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;
using System.Data.OleDb;
namespace DAL
{
    public class NhanVienDAO
    {
        dataHelpper da = new dataHelpper();

        #region"Khởi Tạo Kết nối"
        public SqlConnection khoitaoKN()
        {
            SqlConnection conn = new SqlConnection();
            conn = dataHelpper.taoKN();
            return conn;
        }
        #endregion
        #region ""Phat sinh Mã Nhân viên"
        public string Phatsinh_manv()
        {
            string manv = "NV";
            string sql = "select max(id)+1 from nhanvien";
            int temp = (int)da.ExecuteCalar(sql);
            manv += temp.ToString();
            return manv;
        }
        #endregion
        #region " Thêm Nhân Viên"
        public void themNhanVien(NhanVienDTO NV)
        {
            //da.Mo_KN();
            string sql = "insert into nhanvien(manv,hoten, ngaysinh, diachi, dienthoai, bangcap,mabophan,machucvu) values(";
            sql += "'" + NV.Manv + "',";
            sql += "'" + NV.Tennv + "',";
            sql += "'" + NV.Ngaysinh + "',";
            sql += "'" + NV.Diachi + "',";
            sql += "'" + NV.Dienthoai + "',";
            sql += "'" + NV.Bangcap + "',";
            sql += "'" + NV.Mabophan + "',";
            sql += "'" + NV.Machucvu + "')";
            da.truyvan(sql);
            // da.Dong_KN();
        }
        #endregion
        #region "Xóa Nhân Viên"
        public void xoaNhanVien(NhanVienDTO NV)
        {
            //da.Mo_KN();
            string sql = "delete from nhanvien where manv='" + NV.Manv + "'";
            da.truyvan(sql);
            //da.Dong_KN();
        }
        #endregion
        #region " Sửa thông tin nhân viên"
        public void Sua_NhanVien(NhanVienDTO NV)
        {
            // da.Mo_KN();
            string sql = "update nhanvien set hoten='N" + NV.Tennv + "',";
            sql += "ngaysinh ='" + NV.Ngaysinh + "', ";
            sql += "diachi='" + NV.Diachi + "',";
            sql += "DienThoai='" + NV.Dienthoai + "',";
            sql += "BangCap='" + NV.Bangcap + "',";
            sql += "MaBoPhan='" + NV.Mabophan + "',";
            sql += "Machucvu= '" + NV.Machucvu + "' ";
            sql += "where manv='" + NV.Manv + "' ";
            da.truyvan(sql);
            // da.Dong_KN();
        }
        #endregion
        #region "Tra cứu Thông Tin nhân Viên"
        public DataTable TimKiemTheoMaNV(string manv)
        {
            string sql = "select *from nhanvien where manv like '%" + manv + "%'";
            return da.HienThi(sql);
        }

        public DataTable TimKiemTheoTen(string tennv)
        {
            string sql = "select * from nhanvien where hoten like'%" + tennv + "%'";
            return da.HienThi(sql);

        }
        //public DataTable TimTheoNgaySinh(NhanVienDTO NV)
        //{
        //    string sql = "select * from nhanvien ngaysinh ngaysinh between '#"++"#'";
        //}

        #endregion
        #region"Hien Thi Bảng Nhân viên"
        public DataTable HienThi_DSNV()
        {
            // da.Mo_KN();
            string sql = "select manv, hoten, ngaysinh, diachi, dienthoai,bangcap, mabophan, machucvu from nhanvien";
            return da.HienThi(sql);
            // da.Dong_KN();
        }
        #endregion
        ///------------code thu Excel---ko dc xoa-------------
        ///
        #region "Ket Noi Excel"
        public DataTable Import_FromEXcel()
        {

            OleDbConnection conn = new OleDbConnection();
            OleDbCommand cmd = new OleDbCommand();
            string path = @"F:\nhanvien.xlsx";
            string connectstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path;
            connectstring += ";Extended Properties=Excel 8.0";
            conn.ConnectionString = connectstring;
            cmd.CommandText = "select * from [NHANVIEN$]";
            cmd.Connection = conn;
            DataTable dt = new DataTable();
            OleDbDataAdapter Da_AP = new OleDbDataAdapter(cmd);
            Da_AP.Fill(dt);
            SqlConnection connect2 = dataHelpper.taoKN();
            connect2.Open();
            SqlBulkCopy copy = new SqlBulkCopy(connect2);
            copy.DestinationTableName = "nhanvien";
            copy.ColumnMappings.Add("manv", "manv");
            copy.ColumnMappings.Add("hoten", "hoten");
            copy.ColumnMappings.Add("ngaysinh", "ngaysinh");
            copy.ColumnMappings.Add("diachi", "diachi");
            copy.ColumnMappings.Add("dienthoai", "dienthoai");
            copy.ColumnMappings.Add("bangcap", "bangcap");
            copy.ColumnMappings.Add("mabophan", "mabophan");
            copy.ColumnMappings.Add("machucvu", "machucvu");
            copy.WriteToServer(dt);
            copy.Close();
            return dt;
            connect2.Close();


        }

     }
}
#endregion



